{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/ice%26fire/Ice%26Fire_import.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "R_hqa9eZDO2M",
    "outputId": "2a1256f4-e99b-40bd-c770-1c8a8806bc1b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
      "Collecting graphdatascience\n",
      "  Downloading graphdatascience-1.5-py3-none-any.whl (183 kB)\n",
      "\u001b[K     |████████████████████████████████| 183 kB 13.2 MB/s \n",
      "\u001b[?25hCollecting multimethod<2.0,>=1.0\n",
      "  Downloading multimethod-1.9-py3-none-any.whl (10 kB)\n",
      "Requirement already satisfied: tqdm<5.0,>=4.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (4.64.1)\n",
      "Requirement already satisfied: pyarrow<11.0,>=4.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (6.0.1)\n",
      "Collecting neo4j<6.0,>=4.4.2\n",
      "  Downloading neo4j-5.2.0.tar.gz (173 kB)\n",
      "\u001b[K     |████████████████████████████████| 173 kB 62.5 MB/s \n",
      "\u001b[?25hRequirement already satisfied: pandas<2.0,>=1.0 in /usr/local/lib/python3.7/dist-packages (from graphdatascience) (1.3.5)\n",
      "Requirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from neo4j<6.0,>=4.4.2->graphdatascience) (2022.6)\n",
      "Requirement already satisfied: numpy>=1.17.3 in /usr/local/lib/python3.7/dist-packages (from pandas<2.0,>=1.0->graphdatascience) (1.21.6)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas<2.0,>=1.0->graphdatascience) (2.8.2)\n",
      "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.7.3->pandas<2.0,>=1.0->graphdatascience) (1.15.0)\n",
      "Building wheels for collected packages: neo4j\n",
      "  Building wheel for neo4j (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
      "  Created wheel for neo4j: filename=neo4j-5.2.0-py3-none-any.whl size=248021 sha256=b1213438cc3b276397b7ec7db728e7913e3d1e99d4b729b71520a854edd87042\n",
      "  Stored in directory: /root/.cache/pip/wheels/5a/07/16/4d845d69ef310660c14b7148848c95da3ef3950c7b58daec42\n",
      "Successfully built neo4j\n",
      "Installing collected packages: neo4j, multimethod, graphdatascience\n",
      "Successfully installed graphdatascience-1.5 multimethod-1.9 neo4j-5.2.0\n"
     ]
    }
   ],
   "source": [
    "!pip install graphdatascience"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "Eu_aAgmYiaGj"
   },
   "outputs": [],
   "source": [
    "from graphdatascience import GraphDataScience\n",
    "\n",
    "host = \"bolt://3.231.25.240:7687\"\n",
    "user = \"neo4j\"\n",
    "password = \"hatchets-visitor-axes\"\n",
    "\n",
    "gds = GraphDataScience(host, auth=(user, password))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lZsg0OoBM_8L"
   },
   "source": [
    "# Define constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "rN310oX6lH_4",
    "outputId": "8e51e2be-d2eb-4948-cb4c-569163abc533"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "CREATE CONSTRAINT IF NOT EXISTS FOR (h:Faction) REQUIRE (h.url) IS UNIQUE; \n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "9tgp3ReklRX4",
    "outputId": "18fb0af1-7e87-4759-d30e-e8414b43289d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "CREATE CONSTRAINT IF NOT EXISTS FOR (c:Character) REQUIRE (c.url) IS UNIQUE; \n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TtEaQGp2NB9Q"
   },
   "source": [
    "# Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "JxexrXQsNYUv",
    "outputId": "b13efbd6-8817-4750-9f57-35684249b3ec"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-ab9e8dc9-16ae-4f84-b301-768e70ccf98f\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ab9e8dc9-16ae-4f84-b301-768e70ccf98f')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-ab9e8dc9-16ae-4f84-b301-768e70ccf98f button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-ab9e8dc9-16ae-4f84-b301-768e70ccf98f');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "call dbms.setConfigValue('dbms.transaction.timeout','0')\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "RYXOh0A4lU9R",
    "outputId": "65c98346-9253-443f-82df-70f933039182"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "LOAD CSV WITH HEADERS FROM \"https://raw.githubusercontent.com/tomasonjo/blog-datasets/main/ice_fire/characters.tsv\" AS row FIELDTERMINATOR \"\\t\"\n",
    "MERGE (c:Character {url: toLower(row.url)})\n",
    "SET c.name = row.name,\n",
    "    c.born = replace(replace(replace(row.born, \"[\", \"\"), \"]\", \"\"),\"b'\",\"\"),\n",
    "    c.died = replace(replace(replace(row.died, \"[\", \"\"), \"]\", \"\"),\"b'\",\"\"),\n",
    "    c.title = replace(replace(replace(row.title, \"[\", \"\"), \"]\", \"\"),\"b'\",\"\")\n",
    "FOREACH (a IN apoc.convert.fromJsonList(row.allegiance) | MERGE (f:Faction {url: toLower(split(a, \"//\")[1])}) MERGE (c)-[:ALLEGIANCE]->(f))\n",
    "FOREACH (cu IN apoc.convert.fromJsonList(row.culture)   | MERGE (culture:Culture {name: split(toLower(cu), \"[\")[0]}) MERGE (c)-[:CULTURE]->(culture))\n",
    "FOREACH (s IN apoc.convert.fromJsonList(row.spouse)     | MERGE (c1:Character {url: toLower(s)}) MERGE (c)-[:SPOUSE]-(c1))\n",
    "FOREACH (s IN apoc.convert.fromJsonList(row.father)     | MERGE (c1:Character {url: toLower(s)}) MERGE (c)-[:FATHER]->(c1))\n",
    "FOREACH (m IN apoc.convert.fromJsonList(row.mother)     | MERGE (c1:Character {url: toLower(m)}) MERGE (c)-[:MOTHER]->(c1))\n",
    "FOREACH (b IN apoc.convert.fromJsonList(row.books)      | MERGE (b1:Book {url: toLower(split(b, \"//\")[1])}) MERGE (c)-[:APPEARED_IN_BOOK]->(b1))\n",
    "FOREACH (s IN apoc.convert.fromJsonList(row.show)       | MERGE (s1:Show {url: toLower(split(s, \"//\")[1])}) MERGE (c)-[:APPEARED_IN_BOOK]->(s1))\n",
    "FOREACH (pr IN apoc.convert.fromJsonList(row.predecessor) | MERGE (c1:Character {url: toLower(pr)}) MERGE (c)-[:PREDECESSOR]->(c1))\n",
    "FOREACH (pr IN apoc.convert.fromJsonList(row.successor)   | MERGE (c1:Character {url: toLower(pr)}) MERGE (c)<-[:PREDECESSOR]-(c1))\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6UZeKal7NDm8"
   },
   "source": [
    "# Data cleanup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "TSttWqMYFlP0",
    "outputId": "8f2b5cfe-eb3c-4dc0-e04c-cc0385b14944"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (n)\n",
    "WHERE NOT n.name IS NOT NULL\n",
    "WITH n, replace(split(apoc.text.urldecode(n.url), \"/\")[-1], \"_\", \" \") AS clean_name\n",
    "SET n.name = clean_name\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "cCONUTgssmqY",
    "outputId": "7a08235b-0d32-4b0a-d95a-3d7aa7d64d6f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>selfloops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>329</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   selfloops\n",
       "0        329"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (t)-[r]->(m)\n",
    "WHERE toLower(t.name) = toLower(m.name)\n",
    "DELETE r\n",
    "RETURN count(*) AS selfloops\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "f0VCfRMNJYw-",
    "outputId": "ae6513b1-8357-41fe-ac30-738dee59e9ed"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>isolated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   isolated\n",
       "0       221"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (n) \n",
    "WHERE NOT EXISTS { (n)--() }\n",
    "DELETE n\n",
    "RETURN count(*) AS isolated\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "o59beDs6MXhg",
    "outputId": "c6b050d6-319c-4abc-e896-48c60104da69"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>done</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0   done"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (s:Show)\n",
    "WHERE NOT s.url CONTAINS \"house\"\n",
    "WITH s, split(s.url, \"_\")[-1] AS seasons\n",
    "WITH seasons, collect(s) AS duplicates\n",
    "WHERE size(duplicates) > 1\n",
    "CALL apoc.refactor.mergeNodes(duplicates) YIELD node\n",
    "RETURN distinct 'done' AS result\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "-8o0rpISh_YN",
    "outputId": "76737527-a69a-4b19-b656-ab879c604bf1"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>'done'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>done</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  'done'\n",
       "0   done"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\"\"\"\n",
    "MATCH (c1:Character), (c2:Character)\n",
    "WHERE c1.name CONTAINS \"catelyn tully\" AND c2.name CONTAINS \"Catelyn Stark\"\n",
    "CALL apoc.refactor.mergeNodes([c2,c1]) YIELD node\n",
    "RETURN distinct 'done'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5P1_Yq17NF9s"
   },
   "source": [
    "# Verify the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 142
    },
    "id": "VBziu9xioGFJ",
    "outputId": "1d1200a7-39d1-47b5-eb25-87d93d3af5c6"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>labelCount</th>\n",
       "      <th>relTypeCount</th>\n",
       "      <th>propertyKeyCount</th>\n",
       "      <th>nodeCount</th>\n",
       "      <th>relCount</th>\n",
       "      <th>labels</th>\n",
       "      <th>relTypes</th>\n",
       "      <th>relTypesCount</th>\n",
       "      <th>stats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>86</td>\n",
       "      <td>4319</td>\n",
       "      <td>16941</td>\n",
       "      <td>{'Character': 3653, 'Book': 20, 'Show': 11, 'C...</td>\n",
       "      <td>{'(:Character)-[:PREDECESSOR]-&gt;()': 307, '()-[...</td>\n",
       "      <td>{'APPEARED_IN_BOOK': 7936, 'FATHER': 960, 'MOT...</td>\n",
       "      <td>{'relTypeCount': 7, 'propertyKeyCount': 86, 'l...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   labelCount  relTypeCount  propertyKeyCount  nodeCount  relCount  \\\n",
       "0           7             7                86       4319     16941   \n",
       "\n",
       "                                              labels  \\\n",
       "0  {'Character': 3653, 'Book': 20, 'Show': 11, 'C...   \n",
       "\n",
       "                                            relTypes  \\\n",
       "0  {'(:Character)-[:PREDECESSOR]->()': 307, '()-[...   \n",
       "\n",
       "                                       relTypesCount  \\\n",
       "0  {'APPEARED_IN_BOOK': 7936, 'FATHER': 960, 'MOT...   \n",
       "\n",
       "                                               stats  \n",
       "0  {'relTypeCount': 7, 'propertyKeyCount': 86, 'l...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "CALL apoc.meta.stats()\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "YFaql70MHzTh",
    "outputId": "528e46d4-cfc3-4ef0-bf0c-0c33a397640d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Character</td>\n",
       "      <td>3653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Faction</td>\n",
       "      <td>563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Culture</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Book</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Show</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       label  count\n",
       "0  Character   3653\n",
       "1    Faction    563\n",
       "2    Culture     72\n",
       "3       Book     20\n",
       "4       Show     11"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (n)\n",
    "RETURN labels(n)[0] AS label, count(*) AS count\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "QLkzlxtLH7b_"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
